<?php
function get_connection() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("study_department", $con);
		return $con;
	}
	
	function get_connection_stagingarea() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("stagingarea", $con);
		return $con;
	}
	
	function getConnectionHwSw() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("hw_sw_evidence", $con);
		return $con;
	}

	
echo "Naplnena tabulka: userAccount";
	//naplnanie tabulky usseraccount
	//prida hodnoty casu - pokial sa nenachadzaju uz v tabulke stagingarea.time
	
	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "SELECT creationDate FROM `hw_sw_evidence`.useraccount" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
		list($Date,$DayTime) = split(" ",$row[creationDate]);
		$queryString= "INSERT INTO time (`TimeKey`,`Date`,`DayTime`) VALUES ('".$row[creationDate]."','".$Date."','".$DayTime."') ";
		$queryAnswer = mysql_query($queryString,$connection);
	}
	
	$index=0;
	$connection = get_connection_stagingarea();
			
	$queryString2 = "TRUNCATE useraccount" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 

	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "SELECT u.login, nam.name, s.StudentKey, u.deleted, t.TimeKey, u.locked FROM `hw_sw_evidence`.`useraccount` u
						left join `hw_sw_evidence`.netdomain nam on nam.id_netDomain=u.netDomain_id
						left join `hw_sw_evidence`.person p on p.ais_id=u.person_id
						left join `stagingarea`.student s on s.AIS_ID=p.ais_id
						left join stagingarea.time t on t.TimeKey=u.creationDate" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
			$fieldOfStudyID=0;
			$index++;
				
    		$queryString= "INSERT INTO useraccount (`UserAccountKey`,`StudentKey`,`Username`,`Domain`,`CreationTimeKey`,`Locked`,`Deleted`) VALUES ( 	'".$index."','".$row[StudentKey]."','".$row[login]."','".$row[name]."','".$row[TimeKey]."','".$row[locked]."','".$row[deleted]."') " ; 
			
    		$queryAnswer = mysql_query($queryString,$connection) or die(mysql_error()); 
			
			
	
	}
	//*********************************************************************************
	echo ", Room";
	//naplnenie tabulky Room
	
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE room" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "INSERT INTO `stagingarea`.room (RoomName)
						SELECT `hw_sw_evidence`.`room`.roomName
						FROM `hw_sw_evidence`.`room`;" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
	$connectionHwSw = get_connection();	
	$queryStringHwSw = "SELECT room FROM t_exam GROUP BY room" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
			$queryString2 = "SELECT RoomName FROM room WHERE RoomName='".$row[room]."'" ;
			$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
			$count=0;
			while($row2 = mysql_fetch_array($queryAnswer2) ) {
				$count++;
			}
			if ($count==0){
					$queryString= "INSERT INTO room (`RoomName`) VALUES ( '".$row[room]."') " ; 
			
    		$queryAnswer = mysql_query($queryString,$connection) or die(mysql_error()); 
			}
			
			
			
	
	}
	//**********************************************************************************
	echo ", Computer";
	//naplnenie tabulky Computer
	
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE computer" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "INSERT INTO `stagingarea`.`computer` (RoomKey,NetworkName) SELECT sr.RoomKey,c.networkName FROM `hw_sw_evidence`.`computer` c
						left join `hw_sw_evidence`.`room` hr on hr.id_room=c.room_id
						left join `stagingarea`.`room` sr on sr.RoomName=hr.roomName" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
	
	//**********************************************************************************
	echo ", sessionFacts";
	//sessionFacts
	
	//prida hodnoty casu - pokial sa nenachadzaju uz v tabulke stagingarea.time
	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "SELECT begin,end FROM `hw_sw_evidence`.session" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
		list($Date,$DayTime) = split(" ",$row[begin]);
		$queryString= "INSERT INTO time (`TimeKey`,`Date`,`DayTime`) VALUES ('".$row[begin]."','".$Date."','".$DayTime."') ";
		$queryAnswer = mysql_query($queryString,$connection); 
    	list($Date,$DayTime) = split(" ",$row[end]);
		$queryString= "INSERT INTO time (`TimeKey`,`Date`,`DayTime`) VALUES ('".$row[end]."','".$Date."','".$DayTime."') ";
		$queryAnswer = mysql_query($queryString,$connection); 
    	
	}
	
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE sessionfacts" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "INSERT INTO stagingarea.sessionfacts (UserAccountKey,RoomKey,ComputerKey,StartTimeKey,EndTimeKey) SELECT s.userAccount_id, c.RoomKey,c.ComputerKey, t.TimeKey, t2.TimeKey FROM `hw_sw_evidence`.`session` s
						left join `stagingarea`.`computer` c on c.ComputerKey=s.computer_id
						left join `hw_sw_evidence`.`userAccount` a on a.id_user=s.computer_id
						left join `stagingarea`.time t on t.TimeKey=s.begin
						left join `stagingarea`.time t2 on t2.TimeKey=s.end" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
	//*********************************************************************************************
	echo ", Term";
	//naplnenie tabulky Term
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE term" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	$connectionHwSw = getConnectionHwSw();	
	$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('2000-09-01','2001-02-15','zimny')" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('2001-02-15','2001-06-30','letny')" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
	for($i=1;$i<9;$i++){
		$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('200".$i."-09-01','200".($i+1)."-02-15','zimny')" ;
		$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
		$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('200".($i+1)."-02-15','200".($i+1)."-06-30','letny')" ;
		$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
	}
	$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('2009-09-01','2010-02-15','zimny')" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('2010-02-15','2010-06-30','letny')" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
		for($i=10;$i<12;$i++){
		$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('20".$i."-09-01','20".($i+1)."-02-15','zimny')" ;
		$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
		$queryStringHwSw = "INSERT INTO `stagingarea`.`term` (StartDate,EndDate,TermType) VALUES ('20".($i+1)."-02-15','20".($i+1)."-06-30','letny')" ;
		$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	
	}
	
	//*********************************************************************************************
	echo ", examFacts";
	//naplnenie tabulky examfacts
	//prida hodnoty casu - pokial sa nenachadzaju uz v tabulke stagingarea.time
	$connection = get_connection_stagingarea();
	$queryString2 = "TRUNCATE examfacts" ;
	$queryAnswer2 = mysql_query($queryString2,$connection)or die(mysql_error()); 
	
	$connectionHwSw = get_connection();	
	$queryStringHwSw = "SELECT date FROM t_exam" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
		
		list($Date,$DayTime) = split(" ",$row[date]);
		
		$queryString= "INSERT INTO time (`TimeKey`,`Date`,`DayTime`) VALUES ('".$Date." 00:00:00','".$Date."','00:00:00') ";
		$queryAnswer = mysql_query($queryString,$connection);
    
		
    	
	}
	
	$connectionHwSw = get_connection();	
	$queryStringHwSw = "SELECT ro.RoomKey,e.date, e.result, e.type, sp.ais_id,ssu.name_sk,sst.StudentKey,sc.CourseKey, et.ExamTypeKey, ti.TimeKey FROM `study_department`.`t_exam` e
						left join `study_department`.`t_student_subject` ss on ss.id=e.id
						left join `study_department`.`t_person` sp on sp.id=ss.student_id
						left join `stagingarea`.`student` sst on sst.AIS_ID=sp.ais_id
						left join `study_department`.`t_subject` ssu on ssu.id=ss.subject_id
						left join `stagingarea`.`course` sc on sc.Name=ssu.name_sk
						left join `stagingarea`.`examtype` et on et.Name=e.type
						left join `stagingarea`.`time` ti on ti.Date=e.date
						left join `stagingarea`.`room` ro on ro.RoomName=e.room" ;
	$queryAnswerHwSw = mysql_query($queryStringHwSw,$connectionHwSw)or die(mysql_error()); 
	$connection = get_connection_stagingarea();
	while($row = mysql_fetch_array($queryAnswerHwSw) ) {
			$TermKey=0;
		list($Date,$DayTime) = split(" ",$row[date]);
			$queryString = "SELECT TermKey, StartDate, EndDate FROM term" ;
		$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
		while($row2 = mysql_fetch_array($queryAnswer) ) {
		
			$start_date = $row2["StartDate"];
			$end_date = $row2["EndDate"];
			$exam_date = $Date;
			//echo $Date;
			//echo $start_date;
			//echo $end_date;
			$exam = strtotime($exam_date);
			$termStart = strtotime($start_date);
			$termEnd = strtotime($end_date);
			
			if (($exam > $termStart) && ($exam < $termEnd)) {
    			
				$TermKey=$row2["TermKey"];
    			// break;
			} 
			
		}
	
			$queryString = "INSERT INTO examfacts (StudentKey, RoomKey, ExamTypeKey, CourseKey, TimeKey,TermKey,Assessment) VALUES ('".$row['StudentKey']."','".$row['RoomKey']."','".$row['ExamTypeKey']."','".$row['CourseKey']."','".$row['TimeKey']."','".$TermKey."','".$row['result']."')" ;
			$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	}
	
		/*	$TermKey=0;
		$connection = get_connection_stagingarea();
			$queryString = "SELECT TermKey, StartDate, EndDate FROM term" ;
		$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
		while($row2 = mysql_fetch_array($queryAnswer) ) {
			//echo $row2["StartDate"];
			$start_date = $row2["StartDate"];
			$end_date = $row2["EndDate"];
			$exam_date = $Date;
			
			$exam = strtotime($exam_date);
			$termStart = strtotime($start_date);
			$termEnd = strtotime($end_date);
			
			if (($exam > $termStart) && ($exam<$termEnd)) {
    			 $TermKey=$row2["TermKey"];
    			 //break;
			} 
			
		}*/
//	echo $TermKey;
	
		

	mysql_close($connection);
	mysql_close($connectionHwSw);
				
	/*$connection = get_connection();
		
      			$queryString = "SELECT * FROM t_person WHERE person_type_id=1" ;
				$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
				
				while($row = mysql_fetch_array($queryAnswer) ) {
					$text .= $row['ais_id'];
				
				}
				mysql_close($connection);*/
				
?>
